package com.example.tool.mysql;

import com.example.tool.ck.TextUtils;
import com.mysql.cj.jdbc.result.ResultSetImpl;
import com.mysql.cj.result.Field;

import java.sql.*;
import java.util.*;

public class JdbcUtils {

    public static String queryLocalFoodName(String localSpuId) {
        try {
            Map<String, Object> foodMap = queryOne("select name from dkd_chain.dkd_food_management where id = " + localSpuId + " limit 1");
            return Optional.ofNullable(foodMap.get("name")).orElse("").toString();
        } catch (Exception e) {
            return "";
        }
    }


    public static Map<String,String> queryLocalFoodNames(Collection<String> localSpuIds) {
        Map<String,String> emptyMap  = new HashMap<>();
        for (String localSpuId : localSpuIds) {
            emptyMap.put(localSpuId,"");
        }
        try {
            List<Map<String, Object>>  foodMap = queryList("select id , name from dkd_chain.dkd_food_management where id in (" + String.join(",",localSpuIds)+")" );
            for (Map<String, Object> stringObjectMap : foodMap) {
                emptyMap.put(stringObjectMap.get("id").toString(),Optional.ofNullable(stringObjectMap.get("name")).orElse("").toString());
            }
            return emptyMap;
        } catch (Exception e) {
            return emptyMap;
        }
    }

    public static interface CallBack {
        public List<Map<String, Object>> run(Connection conn, String sql) throws SQLException;
    }
    public static List<Map<String, Object>> queryList(String sql) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = null;
        try {
            //获取数据库连接对象
            conn = getConn("prod");
            return ((CallBack) (conn1, sql1) -> {
                Statement statement = conn1.createStatement();
                ResultSet resultSet = statement.executeQuery(sql1);
                com.mysql.cj.result.Field[] fields = ((ResultSetImpl) resultSet).getMetadata().getFields();
                List<Map<String, Object>> resultMaps = new ArrayList<>();
                while (resultSet.next()) {
                    Map<String, Object> resultMap = new HashMap<>();
                    for (com.mysql.cj.result.Field field : fields) {
                        String name = field.getName();
                        Object object = resultSet.getObject(name);
                        resultMap.put(name, object);
                    }
                    resultMaps.add(resultMap);
                }
                statement.close();
                return resultMaps;
            }).run(conn, sql);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
    }


    public static Map<String, Object> queryOne(String sql) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = null;
        try {
            conn  = getConn("prod");
            List<Map<String, Object>> res = ((CallBack) (conn1, sql1) -> {
                Statement statement = conn1.createStatement();
                ResultSet resultSet = statement.executeQuery(sql1);
                com.mysql.cj.result.Field[] fields = ((ResultSetImpl) resultSet).getMetadata().getFields();
                List<Map<String, Object>> resultMaps = new ArrayList<>();
                while (resultSet.next()) {
                    Map<String, Object> resultMap = new HashMap<>();
                    for (Field field : fields) {
                        String name = field.getName();
                        Object object = resultSet.getObject(name);
                        resultMap.put(name, object);
                    }
                    resultMaps.add(resultMap);
                }
                statement.close();
                return resultMaps;
            }).run(conn, sql);
            return (res == null || res.isEmpty()) ? null : res.get(0);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
    }

    private static Connection getConn(String env) throws SQLException {
        if(env.equals("prod")){
            //获取数据库连接对象
            return DriverManager.getConnection(TextUtils.queryAndGetText("online.mysql.url")
                    , TextUtils.queryAndGetText("online.mysql.username"), TextUtils.queryAndGetText("online.mysql.password"));
        }else{
            return DriverManager.getConnection(TextUtils.queryAndGetText("test.mysql.url")
                    , TextUtils.queryAndGetText("test.mysql.username"), TextUtils.queryAndGetText("test.mysql.password"));
        }
    }
}
